import pymysql, re, time
from selenium import webdriver
from selenium.webdriver import ChromeOptions
from datetime import datetime

settings = {'host': '172.16.10.187', 'user': 'tidb', 'port': 3306, 'password': 'Tidb@guest@123', 'database': 'crawl',
            'charset': 'utf8'}
conn = pymysql.connect(**settings)
print('##################################################数据库连接成功##################################################')
cursor = conn.cursor()
print('##################################################获取游标成功##################################################')
dic = {'欧若拉玫瑰鲜花': '欧若拉','碎冰蓝玫瑰花': '碎冰蓝','伊莎贝拉鲜花': '伊莎贝拉','黑骑士鲜花': '黑骑士','黑玫瑰鲜花': '黑玫瑰',
        '怦然心动玫瑰': '怦然心动玫瑰','欧若拉玫瑰': '欧若拉','碎冰蓝玫瑰': '碎冰蓝','彩虹玫瑰花': '彩虹','伊莎贝拉玫瑰': '伊莎贝拉',
        '黑骑士玫瑰': '黑骑士','卡普里灰紫玫瑰鲜花': '卡普里灰','黑玫瑰花': '黑玫瑰','太妃糖玫瑰鲜花': '太妃糖','卡普里灰': '卡普里灰',
        '太妃糖玫瑰': '太妃糖','玫瑰彩虹鲜花': '彩虹'}
sql = "insert into rose_flower_copy1(channel,classify,create_time,href,index_price,location,people_buy,search,shop,title,url) values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"
channel = '淘宝'

# def init_driver(driver):
#     url = 'https://www.taobao.com/'
#     driver.get(url)
#     driver.find_element_by_xpath('//*[@id="J_SiteNavLogin"]/div[1]/div[1]/a[1]').click()
#     driver.find_element_by_xpath('//input[@id="fm-login-id"]').send_keys('')
#     driver.find_element_by_xpath('//input[@id="fm-login-password"]').send_keys('')
#     driver.find_element_by_xpath('//*[@id="login-form"]/div[4]/button').click()
#     return driver

def save_list(driver):
    sql = "insert into rose_flower_copy1(channel,classify,create_time,href,index_price,location,people_buy,search,shop,title,url) values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"
    # 首页搜索输入框输入
    driver.find_element_by_xpath('//input[@id="q"]').send_keys('欧若拉玫瑰鲜花')
    driver.find_element_by_xpath('//*[@id="J_TSearchForm"]/div[1]').click()
    for search, classify in dic.items():
        # 清空搜索
        driver.find_element_by_xpath('//input[@id="q"]').clear()
        # 列表页搜索框输入东西
        driver.find_element_by_xpath('//input[@id="q"]').send_keys(search)
        # 列表页点击搜索
        driver.find_element_by_xpath('//*[@id="J_SearchForm"]/button').click()
        # 点击销量
        driver.find_element_by_xpath('//*[@id="J_relative"]/div[1]/div/ul/li[2]/a').click()
        # 等待加载
        time.sleep(3)
        elements = driver.find_elements_by_xpath('//*[@id="mainsrp-itemlist"]/div/div/div[@class="items"]/div')
        # dic = {'欧若拉': '欧若拉玫瑰鲜花', '棉花糖': '棉花糖玫瑰鲜花',''}
        for element in elements[:10]:
            href = element.find_element_by_xpath('./div[2]/div[2]/a').get_attribute('href')
            title = element.find_element_by_xpath('./div[2]/div[2]/a').text
            people_buy = element.find_element_by_xpath('./div[2]/div[1]/div[@class="deal-cnt"]').text
            shop = element.find_element_by_xpath('./div[2]/div[3]/div[@class="shop"]/a/span[2]').text
            location = element.find_element_by_xpath('./div[2]/div[3]/div[@class="location"]').text
            index_price = element.find_element_by_xpath('./div[2]/div[1]/div[1]/strong').text
            url = 'https://www.taobao.com/'
            search = search
            classify = classify
            channel = '淘宝'
            create_time = datetime.now()
            row = (channel, classify, create_time, href, index_price, location, people_buy, search, shop, title, url)
            print(row)
            cursor.execute(sql, row)
    return 'The list page is saved successfully!'

def save_detail(driver):
    select_sql = 'SELECT average_price,channel,classify,create_time,href,index_price,location,number,people_buy,detail_price,search,shop,title,url FROM rose_flower_copy1'
    cursor.execute(select_sql)
    data = cursor.fetchall()
    for i in data:
        driver.get(i[4])
        # 天猫店铺分类
        try:
            if 'detail.tmall.com' in i[4]:
                n = driver.find_elements_by_xpath('//div[@class="tb-sku"]/dl[1]/dd[1]/ul[1]/li')
                for j in range(len(n)):
                    number = None
                    average_price = None
                    driver.find_elements_by_xpath('//div[@class="tb-sku"]/dl/dd/ul/li')[j].click()
                    try:
                        sale_num = driver.find_element_by_xpath(
                            '//*[@id="J_DetailMeta"]/div[1]/div[1]/div/ul/li[1]/div/span[2]').text
                    except:
                        sale_num = None
                    try:
                        info = driver.find_elements_by_xpath('//div[@class="tb-sku"]/dl[1]/dd[1]/ul[1]/li')[j].text
                        price = driver.find_element_by_xpath('//*[@id="J_PromoPrice"]/dd/div/span').text
                    except:
                        price = driver.find_element_by_xpath('//*[@id="J_StrPriceModBox"]/dd/span').text
                    if len(re.findall('\d+', info)) > 0:
                        try:
                            number = re.findall('\d+', info)[0]
                            average_price = float(price) / float(number)
                        except:
                            pass
                    sql = "insert into rose_flower_copy2(sale_num,average_price,channel,classify,create_time,href,index_price,location,number,people_buy,detail_price,search,shop,title,url,info) values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"
                    channel = '天猫'
                    row = (
                        sale_num, average_price, channel, i[2], i[3], i[4], i[5], i[6], number, i[8], price, i[10], i[11],
                        i[12], i[13], info)
                    cursor.execute(sql, row)
                    conn.commit()
                    print(info)
                    print(price)
            # 淘宝店铺分类
            else:
                n = driver.find_elements_by_xpath('//*[@id="J_isku"]/div/dl[1]/dd/ul/li/a')
                for j in range(len(n)):
                    driver.find_elements_by_xpath('//*[@id="J_isku"]/div/dl[1]/dd/ul/li/a')[j].click()
                    try:
                        sale_num = driver.find_element_by_xpath('//*[@id="J_SellCounter"]').text
                    except:
                        sale_num = None
                    try:
                        info = driver.find_element_by_xpath('//*[@class="ks-overlay-content"]').get_attribute('textContent')
                    except:
                        info = None
                    number = None
                    average_price = None
                    if not info:
                        info = driver.find_element_by_xpath('//*[@id="J_isku"]/div/dl[1]/dd/ul/li/a/span')[j].text
                    price = driver.find_element_by_xpath('//*[@id="J_StrPrice"]/em[2]').text
                    if len(re.findall('\d+', info)) > 0:
                        try:
                            number = re.findall('\d+', info)[0]
                            average_price = float(price) / float(number)
                        except:
                            pass
                    channel = '淘宝'
                    sql = "insert into rose_flower_copy2(sale_num,average_price,channel,classify,create_time,href,index_price,location,number,people_buy,detail_price,search,shop,title,url,info) values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"

                    row = (
                        sale_num, average_price, channel, i[2], i[3], i[4], i[5], i[6], number, i[8], price, i[10], i[11],
                        i[12], i[13], info)
                    cursor.execute(sql, row)
                    conn.commit()
                    print(info)
                    print(price)
        except Exception as e:
            print(e)
    return 'The details page is saved successfully!'


if __name__ == '__main__':
    option = ChromeOptions()
    option.add_experimental_option('excludeSwitches', ['enable-automation'])
    prefs = {}
    prefs["credentials_enable_service"] = False
    prefs["profile.password_manager_enabled"] = False
    option.add_experimental_option("prefs", prefs)
    option.add_argument('--ignore-certificate-errors')
    driver = webdriver.Chrome(executable_path='chromedriver', options=option)
    driver.execute_cdp_cmd("Page.addScriptToEvaluateOnNewDocument", {
        "source": """
    Object.defineProperty(navigator, 'webdriver', {
    get: () => undefined
    })
    """
    })
    # driver = init_driver(driver)
    save_list(driver)
    # save_detail(driver)
    cursor.close()
    conn.close()
